package com.cheeven.cwliu.citation.analysis;

import java.io.FileReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {

	static String DRIVER_NAME = "org.apache.derby.jdbc.EmbeddedDriver";
	static String DB_NAME = "crDB";
	static int RAW_DATA_COLUMN_COUNT = 135; // excluding ID, FILENAME, ROWNUMBER
	static String CREATE_RAW_DATA_TABLE_SQL = loadSQLCode("sql\\create_raw_data_table.sql");
	static String DELETE_OLD_DATA_IN_RAW_DATA_TABLE_SQL = loadSQLCode("sql\\delete_old_data_in_raw_data_table.sql");
	static String FIND_OLD_DATA_IN_RAW_DATA_TABLE_SQL = loadSQLCode("sql\\find_old_data_in_raw_data_table.sql");
	static String INSERT_RAW_DATA_SQL = loadSQLCode("sql\\insert_raw_data_table.sql");

	private static String loadSQLCode(String filename) {
		try {
			FileReader in = new  FileReader(filename);
			StringWriter out = new StringWriter();
			char[] buffer = new char[256];
			int c = in.read(buffer);
			while(c!=-1) {
				out.write(buffer, 0, c);
				c = in.read(buffer);
			}
			in.close();
			out.flush();
			return out.toString();
		} catch(Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	
	public static Connection create() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER_NAME);
		Connection conn = DriverManager.getConnection("jdbc:derby:" + DB_NAME + ";create=true");
		return conn;
	}

	public static Connection connect() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER_NAME);
		Connection conn = DriverManager.getConnection("jdbc:derby:" + DB_NAME);
		return conn;
	}

	public static boolean exit(Connection conn) {
		try {
			conn.close();
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			if("XJ015".equals(e.getSQLState())) {
				return true;
			}
			throw new RuntimeException(e);
		}
		return false;
	}

	public static void createCitationTable(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(CREATE_RAW_DATA_TABLE_SQL);
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Connection conn = create();
		createCitationTable(conn);
		exit(conn);
	}
	
}
